package com.qufadai.framework.orm.jdbc;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

public class JdbcUnit {
    private DataSource dataSource;
    private Map<String, TableDefinition> dbMap = new HashMap<String, TableDefinition>();
    
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    public static void main(String[] args) throws SQLException {
		BasicDataSource ds = new BasicDataSource();
		ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		ds.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
		ds.setUsername("nutzshop");
		ds.setPassword("nutzshop");
		ds.setDefaultAutoCommit(false);
		JdbcUnit ju = new JdbcUnit();
		ju.setDataSource(ds);
		ju.createTable(com.qufadai.framework.orm.jdbc.Test.class);
		Test t = new Test();
		t.setId(1000);
		t.setUsername("wesnow");
		Calendar c = Calendar.getInstance();
		c.set(2011, 1, 2, 10, 12, 24);
		t.setCreateDate(c.getTime());
		//ju.insert(t, "Test");
		Object obj = ju.get("1000", "Test", Test.class);
		System.out.println(obj);
    }
    
    public void createTable(Class<?> mappingClass) throws SQLException {
        TableDefinition tableDef = checkCached(mappingClass);
        String sqlScript = JdbcParser.parseTable(tableDef);
        if(tableDef == null || sqlScript == null) {
            return;
        }
        Connection con = dataSource.getConnection();
        Statement st = null;
        try {
            if(isExist(con,tableDef.getTableName())) {
                return;
            }
            con.setAutoCommit(false);
            st = con.createStatement();
            st.execute(sqlScript);
            con.commit();
        } catch(SQLException ex) {
            con.rollback();
            throw ex;
        } finally {
            JdbcUtil.closeJdbc(con,st);
        }
    }
    
    @SuppressWarnings("rawtypes")
	public Object get(String id, String key, Class clazz) throws SQLException {
        TableDefinition tableDef = dbMap.get(key);
        if(tableDef == null) {
            throw new SQLException("Exception: table "+key+" doesn't exist");
        }
        String sqlScript = JdbcParser.parserQuery(id,tableDef.getTableName());
        Connection con = dataSource.getConnection();
        con.setReadOnly(true);
        Statement st = null;
        Object entity = null;
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery(sqlScript);
            entity = clazz.newInstance();
            if(rs.next()) {
                fillValue(rs,entity,tableDef,tableDef.getColumns().keySet());
            } else {
                return null;
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeJdbc(con,st);
        }
        return entity;
    }
    
    @SuppressWarnings("rawtypes")
	public List<Object> getAll(String key, Class clazz) throws SQLException {
        TableDefinition tableDef = dbMap.get(key);
        if(tableDef == null) {
            throw new SQLException("Exception: table "+key+" doesn't exist");
        }
        String sqlScript = JdbcParser.parserQuery(tableDef.getTableName());
        Connection con = dataSource.getConnection();
        con.setReadOnly(true);
        PreparedStatement pst = null;
        List<Object> result = new ArrayList<Object>();
        Object entity = null;
        try {
            pst = con.prepareStatement(sqlScript);
            ResultSet rs = pst.executeQuery();
            while(rs.next()) {
                entity = clazz.newInstance();
                fillValue(rs, entity,tableDef, tableDef.getColumns().keySet());
                result.add(entity);
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeJdbc(con, pst);
        }
        
        return result;
    }
    
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<Object> queryByDefinition(Class clazz, QueryDefinition query) throws Exception {
        TableDefinition tableDef = dbMap.get(query.getKey());
        if(tableDef == null) {
            throw new SQLException("Exception: table "+query.getKey()+" doesn't exist");
        }
        String sqlScript = JdbcParser.parserQuery(query,tableDef);
        Connection con = dataSource.getConnection();
        con.setReadOnly(true);
        Statement st = null;
        List result = new ArrayList();
        Object entity = null;
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery(sqlScript);
            while(rs.next()) {
                entity = clazz.newInstance();
                fillValue(rs,entity, tableDef, query.getColumns());
                result.add(entity);
            }
        } finally {
            JdbcUtil.closeJdbc(con, st);
        }
        
        return result;
    }

    public void insert(Object entity, String key) throws SQLException {
        TableDefinition tableDef = dbMap.get(key);
        if(tableDef == null) {
            throw new SQLException("Exception: table "+key+" doesn't exist");
        }
        String sqlScript = JdbcParser.parseInsert(tableDef);
        Connection con = dataSource.getConnection();
        con.setAutoCommit(false);
        PreparedStatement pst = null;
        try {
            pst = con.prepareStatement(sqlScript);
            fillArgs(entity, tableDef, pst, false);
            pst.execute();
            con.commit();
        } catch(SQLException ex) {
            con.rollback();
            throw ex;
        } finally {
            JdbcUtil.closeJdbc(con,pst);
        }
    }
    
    public void update(Object entity, String key) throws SQLException {
        TableDefinition tableDef = dbMap.get(key);
        if(tableDef == null) {
            throw new SQLException("Exception: table "+key+" doesn't exist");
        }
        String sqlScript = JdbcParser.parserUpdate(tableDef);
        Connection con = dataSource.getConnection();
        con.setAutoCommit(false);
        PreparedStatement pst = null;
        try {
            pst = con.prepareStatement(sqlScript);
            fillArgs(entity, tableDef, pst, true);
            pst.execute();
            con.commit();
        } catch(SQLException ex) {
            con.rollback();
            throw ex;
        } finally {
            JdbcUtil.closeJdbc(con, pst);
        }
    }
    
    public void delete(String id, String key) throws SQLException {
        TableDefinition tableDef = dbMap.get(key);
        String sqlScript = JdbcParser.parseDelete(tableDef.getTableName(),id);
        Connection con = dataSource.getConnection();
        con.setAutoCommit(false);
        Statement st = null;
        try {
            st = con.createStatement();
            st.executeUpdate(sqlScript);
            con.commit();
        } catch(SQLException ex) {
            con.rollback();
            throw ex;
        } finally {
            JdbcUtil.closeJdbc(con,st);
        }
    }
    
    public void executeSQL(String sqlScript)throws SQLException{
        Connection con = dataSource.getConnection();
        con.setAutoCommit(false);
        Statement st = null;
        try{
            st = con.createStatement();
            st.executeUpdate(sqlScript);
            con.commit();
        }catch(SQLException ex){
            con.rollback();
            throw ex;
        }finally{
            JdbcUtil.closeJdbc(con,st);
        }
    }
    
    public boolean checkSQL(String sqlScript) throws SQLException{
        Connection con = dataSource.getConnection();
        con.setReadOnly(true);
        Statement st = null;
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery(sqlScript);
            if(rs.next()) {
                return true;
            } else {
                return false;
            }
        } finally {
            JdbcUtil.closeJdbc(con,st);
        }
    }
    
    public Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }
    
    public List<String> executeList(String sqlScript) throws SQLException{
        Connection con = dataSource.getConnection();
        con.setReadOnly(true);
        Statement st = null;
        List<String> list = new ArrayList<String>();
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery(sqlScript);
            while(rs.next()) {
                list.add(rs.getString(1));
            }
            return list;
        } finally {
            JdbcUtil.closeJdbc(con,st);
        }
    }
    
    public Object executeQuery(String sqlScript, int type)throws SQLException{
        Connection con  = dataSource.getConnection();
        con.setReadOnly(true);
        PreparedStatement pst = null;
        Object value = null;
        try {
            pst = con.prepareStatement(sqlScript);
            ResultSet rs = pst.executeQuery();
            if(!rs.next()) {
                return null;
            }
            switch(type) {
            case ReflectParser.INT:
                value = rs.getInt(1);
                break;
            case ReflectParser.LONG:
                value = rs.getLong(1);
                break;
            case ReflectParser.FLOAT:
                value = rs.getFloat(1);
                break;
            case ReflectParser.DOUBLE:
                value = rs.getDouble(1);
                break;
            case ReflectParser.STRING:
                value = rs.getString(1);
                break;
            case ReflectParser.BINARY:
                InputStream input = rs.getBinaryStream(1);
                ByteArrayOutputStream output = new ByteArrayOutputStream(10240);
                byte[] b = new byte[1024];
                int len = b.length;
                try{
                    while ((len = input.read(b)) >= 0)
                        output.write(b, 0, len);
                    value = output.toByteArray();
                }catch(IOException ex){
                    ex.printStackTrace();
                    value = null;
                }
                break;
            }
        }finally{
            JdbcUtil.closeJdbc(con,pst);
        }
        
        return value;
    }
    
    private void fillArgs(Object entity, TableDefinition tableDef, PreparedStatement pst, boolean isUpdate) throws SQLException {
        Set<String> keys = tableDef.getColumns().keySet();
        int index = 1;
        int type = 0;
        Object value = null;
        for(String key : keys) {
            if(isUpdate && "id".equalsIgnoreCase(key)) {
                continue;
            }
            value = ReflectParser.getValue(entity,key);
            type = ReflectParser.getType(tableDef.getColType().get(key));
            if(value == null && type == ReflectParser.STRING) {
                pst.setString(index++, "");
                continue;
            } else if(value == null && type == ReflectParser.BINARY) {
                byte[] data = {};
                InputStream input = new ByteArrayInputStream(data);
                pst.setBinaryStream(index++,input,0);
                continue;
            }
            switch(type) {
            case ReflectParser.INT:
                pst.setInt(index++, (Integer)value);
                break;
            case ReflectParser.LONG:
                pst.setLong(index++, (Long)value);
                break;
            case ReflectParser.FLOAT:
                pst.setFloat(index++, (Float)value);
                break;
            case ReflectParser.DOUBLE:
                pst.setDouble(index++, (Double)value);
                break;
            case ReflectParser.STRING:
                pst.setString(index++, (String)value);
                break;
            case ReflectParser.DATE:
            	pst.setDate(index++, new java.sql.Date(((Date)value).getTime()));
            	break;
            case ReflectParser.TIMESTAMP:
            	pst.setTimestamp(index++, new Timestamp(((Date)value).getTime()));
            	break;
            case ReflectParser.BINARY:
                byte[] data = (byte[])value;
                InputStream input = new ByteArrayInputStream(data);
                pst.setBinaryStream(index++,input,data.length);
                break;
            case ReflectParser.NOT_SUPPORT:
                break;
            }
        }//end of for
        if(isUpdate) {
            value = ReflectParser.getValue(entity,"id");
            pst.setString(index++, (String)value);
        }
    }
    
    private void fillValue(ResultSet rs, Object entity, TableDefinition def, Set<String> keys) throws SQLException {
        String column = null;
        Object value = null;
        int type = 0;
        for(String field : keys){
            column = def.getColumns().get(field);
            type = ReflectParser.getType(def.getColType().get(field));
            switch(type){
            case ReflectParser.INT:
                value = rs.getInt(column);
                break;
            case ReflectParser.LONG:
                value = rs.getLong(column);
                break;
            case ReflectParser.FLOAT:
                value = rs.getFloat(column);
                break;
            case ReflectParser.DOUBLE:
                value = rs.getDouble(column);
                break;
            case ReflectParser.STRING:
                value = rs.getString(column);
                break;
            case ReflectParser.DATE:
            	value = rs.getDate(column);
            	break;
            case ReflectParser.TIMESTAMP:
            	value = rs.getTimestamp(column);
            	break;
            case ReflectParser.BINARY:
                InputStream input = rs.getBinaryStream(column);
                ByteArrayOutputStream output = new ByteArrayOutputStream(10240);
                byte[] b = new byte[1024];
                int len = b.length;
                try{
                    while ((len = input.read(b)) >= 0)
                        output.write(b, 0, len);
                    value = output.toByteArray();
                }catch(IOException ex){
                    ex.printStackTrace();
                    value = null;
                }
                break;
            }
            
            ReflectParser.setValue(entity,field,value);
        }//end of for
    }
    
    private boolean isExist(Connection con , String tableName) throws SQLException {
    	ResultSet rs = con.getMetaData().getTables(null, null, null, null);
    	while (rs.next()) {
    		if (rs.getString("TABLE_NAME").equalsIgnoreCase(tableName)) {
    			return true;
    		}
    	}
    	
    	return false;
    }
    
    private TableDefinition checkCached(Class<?> target) {
    	String className = target.getName();
    	int index = className.lastIndexOf(".");
    	String key = className.substring(index+1);
    	TableDefinition def = null;
    	if(dbMap.keySet().contains(key)) {
    		def = dbMap.get(key);
    	} else {
    		def = JdbcParser.parseClass(target);
    		dbMap.put(key, def);
    	}
    	return def;
    }
}
